package de.ts.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.GregorianCalendar;
import java.util.LinkedHashMap;
import java.util.List;

import de.ts.fremdsprache.User;

public class Database {

	public final static String TABLE_NAME = "Fremdsprachenzertifikat_Anmeldungen";
	public final static String COL_ID = "id";
	public final static String COL_FIRSTNAME = "firstname";
	public final static String COL_NAME = "name";
	public final static String COL_GENDER = "gender";
	public final static String COL_SCHOOL = "school";
	public final static String COL_EDUCATION = "education";
	public final static String COL_TESTTYPE = "testtype";
	public final static String COL_BIRTHDAY = "birthday";
	public final static String COL_BIRTHPLACE = "birthplace";
	public final static String COL_GRADE = "grade";
	public final static String COL_POSTALNUMBER = "postalnumber";
	public final static String COL_CITY = "city";
	public final static String COL_ADDRESS = "address";
	public final static String COL_PHONENUMBER = "phonenumber";
	public final static String COL_EMAIL = "email";
	public final static String COL_FAXNUMBER = "faxnumber";
	public final static String COL_COMPANYNAME = "companyname";
	public final static String COL_COMPANYADDRESS = "companyaddress";
	public final static String COL_COMPANYFAX = "companyfax";
	public final static String COL_COMPANYPOSTALNUMBER = "companypostalnumber";
	public final static String COL_COMPANYCITY = "companycity";
	public final static String COL_COMPANYCONTACT = "companycontact";
	public final static String COL_COMPANYPHONENUMBER = "companyphonenumber";

	private Connection connection = null;
	private String url;

	/**
	 * Constructor for Database. Sets the URL of the SQLite-Database.
	 * 
	 * @param filename
	 *            File to database.
	 */
	public Database(String filename) {
		url = "jdbc:sqlite:" + filename;
	}

	/**
	 * Builds up a connection to the SQLite-Database using sqlite-jdbc-3.7.2
	 * driver.
	 */
	public void connect() {

		try {
			Class.forName("org.sqlite.JDBC").newInstance();
			connection = DriverManager.getConnection(url);
			System.out.println("Database connection established");
		} catch (Exception e) {
			disconnect();
			throw new DAOException("Cannot connect to sql-database!" + e.getClass().getName());
		}
	}

	/**
	 * Destroys the connection to the SQLite-Database.
	 */
	public void disconnect() {
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) { /* ignore close errors */
			}
		}
	}

	/**
	 * Checks whether the connection to the SQLite-Database remains.
	 * 
	 * @throws SQLException
	 */
	private void ensureConnection() throws SQLException {
		if (null == connection) {
			throw new SQLException("Not connnected");
		}
	}

	/**
	 * Drops the table with name defined in constant "TABLE_NAME".
	 * 
	 * @throws SQLException
	 */
	public void dropTables() throws SQLException {
		ensureConnection();
		Statement stmt = connection.createStatement();
		stmt.execute("DROP TABLE IF EXISTS " + TABLE_NAME + ";");
	}

	/**
	 * Creates a new table if it doesn't exist. The table name and the row names
	 * are defined in the constants in this class.
	 * 
	 * @throws SQLException
	 */
	public void createTable() throws SQLException {
		ensureConnection();
		Statement stmt = connection.createStatement();
		stmt.execute("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" + COL_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT," + COL_FIRSTNAME
				+ " VARCHAR NOT NULL," + COL_NAME + " VARCHAR NOT NULL,"
				+ COL_GENDER + " ENUM('MALE','FEMALE') NOT NULL," + COL_SCHOOL
				+ " VARCHAR NOT NULL," + COL_EDUCATION + " VARCHAR NOT NULL,"
				+ COL_TESTTYPE + " VARCHAR NOT NULL," + COL_BIRTHDAY
				+ " VARCHAR NOT NULL," + COL_BIRTHPLACE + " VARCHAR NOT NULL,"
				+ COL_GRADE + " VARCHAR NOT NULL," + COL_POSTALNUMBER
				+ " VARCHAR NOT NULL," + COL_CITY + " VARCHAR NOT NULL,"
				+ COL_ADDRESS + " VARCHAR NOT NULL," + COL_PHONENUMBER
				+ " VARCHAR NOT NULL," + COL_EMAIL + " VARCHAR NOT NULL,"
				+ COL_FAXNUMBER + " VARCHAR," + COL_COMPANYNAME + " VARCHAR,"
				+ COL_COMPANYADDRESS + " VARCHAR," + COL_COMPANYFAX
				+ " VARCHAR," + COL_COMPANYPOSTALNUMBER + " VARCHAR,"
				+ COL_COMPANYCITY + " VARCHAR," + COL_COMPANYCONTACT
				+ " VARCHAR," + COL_COMPANYPHONENUMBER + " VARCHAR);");
	}

	/**
	 * Returns the count of entries by setting up a sql-request.
	 * 
	 * @return count of entries
	 * @throws SQLException
	 */
	public int countEntries() throws SQLException {
		ensureConnection();
		Statement stmt = connection.createStatement();
		stmt.execute("SELECT COUNT(*) FROM " + TABLE_NAME + ";");
		return (int) stmt.getResultSet().getLong(1);
	}

	/**
	 * Returns a whole by index specific entry from the database.
	 * 
	 * @param number
	 *            Index of the entry
	 * @return whole entry
	 * @throws SQLException
	 */
	public User getEntry(int number) throws SQLException {
		ensureConnection();
		PreparedStatement stmt = connection.prepareStatement("SELECT * FROM "
				+ TABLE_NAME + " WHERE(" + COL_ID + " = ?) ;");
		stmt.setInt(1, number);
		ResultSet result = stmt.executeQuery();
		User entry = new User();
		entry.setFirstName(result.getString(2));
		entry.setName(result.getString(3));
		entry.setGender(result.getString(4));
		entry.setSchool(result.getString(5));
		entry.setEducation(result.getString(6));
		entry.setTestType(result.getString(7));
		GregorianCalendar calendar = new GregorianCalendar();
		calendar.setTime(result.getDate(8));
		entry.setBirthplace(result.getString(9));
		entry.setGrade(result.getString(10));
		entry.setPostalnumber(result.getString(11));
		entry.setCity(result.getString(12));
		entry.setAddress(result.getString(13));
		entry.setPhonenumber(result.getString(14));
		entry.setEMail(result.getString(15));
		entry.setFaxnumber(result.getString(16));
		entry.setCompanyName(result.getString(17));
		entry.setCompanyAddress(result.getString(18));
		entry.setCompanyFax(result.getString(19));
		entry.setCompanyPostalnumber(result.getString(20));
		entry.setCompanyCity(result.getString(21));
		entry.setCompanyContact(result.getString(22));
		entry.setCompanyPhonenumber(result.getString(23));
		return entry;
	}

	public void insertUser(User user) throws SQLException {
		ensureConnection();
		PreparedStatement stmt = connection
				.prepareStatement("INSERT INTO "
						+ TABLE_NAME
						+ "( "
						+ COL_FIRSTNAME
						+ ", "
						+ COL_NAME
						+ ", "
						+ COL_GENDER
						+ ", "
						+ COL_SCHOOL
						+ ", "
						+ COL_EDUCATION
						+ ", "
						+ COL_TESTTYPE
						+ ", "
						+ COL_BIRTHDAY
						+ ", "
						+ COL_BIRTHPLACE
						+ ", "
						+ COL_GRADE
						+ ", "
						+ COL_POSTALNUMBER
						+ ", "
						+ COL_CITY
						+ ", "
						+ COL_ADDRESS
						+ ", "
						+ COL_PHONENUMBER
						+ ", "
						+ COL_EMAIL
						+ ", "
						+ COL_FAXNUMBER
						+ ", "
						+ COL_COMPANYNAME
						+ ", "
						+ COL_COMPANYADDRESS
						+ ", "
						+ COL_COMPANYFAX
						+ ", "
						+ COL_COMPANYPOSTALNUMBER
						+ ", "
						+ COL_COMPANYCITY
						+ ", "
						+ COL_COMPANYCONTACT
						+ ", "
						+ COL_COMPANYPHONENUMBER
						+ ", "
						+ COL_COMPANYADDRESS
						+ " ) "
						+ "VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
	}
}
